package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.telecom.cms.dto.CampCampSeq;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class CampCampSeqDAO {
	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampCampSeq transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".Camp_Camp_Seq where camp_Seq_Id="
				+ transientInstance.getCampSeqId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Camp_Seq set latn_id='"
						+ transientInstance.getLatnId() + "',camp_id='"
						+ transientInstance.getCampId() + "',camp_seq_name='"
						+ transientInstance.getCampSeqName() + "',caller_num='"
						+ transientInstance.getCallerNum()
						+ "',target_cust_num='"
						+ transientInstance.getTargetCustNum()
						+ "',is_appoint_staff='"
						+ transientInstance.getIsAppointStaff()
						+ "',is_allocate_list='"
						+ transientInstance.getIsAllocateList()
						+ "',is_camp_start='"
						+ transientInstance.getIsCampStart() + "',remark='"
						+ transientInstance.getRemark() + "',Creator_Name='"
						+ transientInstance.getCreatorName()
						+ "',Create_Time=timestamp '"
						+ CurrentTime.getCurrentTime() + "' where camp_Seq_Id="
						+ transientInstance.getCampSeqId();
			else
				sql = "insert into " + db + ".Camp_Camp_Seq values(, '"
						+ transientInstance.getLatnId() + " ' , ' "
						+ transientInstance.getCampId() + " ' , '"
						+ transientInstance.getCampSeqName() + " ' , ' "
						+ transientInstance.getCallerNum() + " ' , ' "
						+ transientInstance.getTargetCustNum() + " ' , ' "
						+ transientInstance.getIsAppointStaff() + " ' , ' "
						+ transientInstance.getIsAllocateList() + " ' , ' "
						+ transientInstance.getIsCampStart() + " ' , '"
						+ transientInstance.getRemark() + " ' , '"
						+ transientInstance.getCreatorName() + " ',  "
						+ "timestamp '" + CurrentTime.getCurrentTime() + "')";
			rs = dao.insert(sql);

			/*if (transientInstance.getCampSeqId() > 0) {
				sql = "delete from td_cms.CAMP_CUST_APPD_RSLT "
						+ "where Camp_Seq_Id="
						+ transientInstance.getCampSeqId();
				rs = dao.insert(sql);

				sql = "update td_cms.CAMP_CUST_APPD_RSLT_TEMP "
						+ "set Camp_Seq_Id=0 " + "where Camp_Seq_Id="
						+ transientInstance.getCampSeqId();
				rs = dao.insert(sql);

				sql = "insert into td_cms.CAMP_CUST_APPD_RSLT "
						+ "select  Seq_Id,Latn_Id,Cust_Id,Prd_Inst_Id,Accs_nbr,"
						+ "All_Accs_Nbr,Clu_Type,Camp_Id,"
						+ transientInstance.getCampSeqId()
						+ ",User_Id from  "
						+ "td_cms.CAMP_CUST_APPD_RSLT_TEMP "
						+ "where (Camp_Seq_Id is null or Camp_Seq_Id<1) sample "
						+ transientInstance.getTargetCustNum();
				rs = dao.insert(sql);

				sql = "update td_cms.CAMP_CUST_APPD_RSLT_TEMP "
						+ "set Camp_Seq_Id="
						+ transientInstance.getCampSeqId()
						+ " where seq_id in(select seq_id from td_cms.CAMP_CUST_APPD_RSLT where Camp_Seq_Id="
						+ transientInstance.getCampSeqId() + ") ";
				rs = dao.insert(sql);
			}*/
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public int insertnum(CampCampSeq transientInstance) {
		int rs = 0;
		String sql;
		try {
			if (transientInstance.getCampSeqId() > 0) {
				sql = "delete from "+db+".CAMP_CUST_APPD_RSLT "
						+ "where Camp_Seq_Id="
						+ transientInstance.getCampSeqId();
				rs = dao.insert(sql);

				sql = "update "+db+".CAMP_CUST_APPD_RSLT_TEMP "
						+ "set Camp_Seq_Id=0 " + "where Camp_Seq_Id="
						+ transientInstance.getCampSeqId();
				rs = dao.insert(sql);

				sql = "insert into "+db+".CAMP_CUST_APPD_RSLT "
						+ "select  Seq_Id,Latn_Id,Cust_Id,Prd_Inst_Id,Accs_nbr,"
						+ "All_Accs_Nbr,Clu_Type,Camp_Id,"
						+ transientInstance.getCampSeqId()
						+ ",User_Id from  "
						+ db+".CAMP_CUST_APPD_RSLT_TEMP "
						+ "where camp_id="+transientInstance.getCampId()+" and (Camp_Seq_Id is null or Camp_Seq_Id<1) sample "
						+ transientInstance.getTargetCustNum();
				rs = dao.insert(sql);

				sql = "update "+db+".CAMP_CUST_APPD_RSLT_TEMP "
						+ "set Camp_Seq_Id="
						+ transientInstance.getCampSeqId()
						+ " where seq_id in(select seq_id from "+ db+".CAMP_CUST_APPD_RSLT where Camp_Seq_Id="
						+ transientInstance.getCampSeqId() + ") ";
				rs = dao.insert(sql);
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		return rs;
	}

	public void delete(CampCampSeq persistentInstance) {
		String sql1 = "delete from " + db + ".Camp_Camp_Seq where camp_Seq_Id = " + persistentInstance.getCampSeqId();
		String sql2 = "delete from " + db + ".CAMP_CUST_APPD_RSLT where camp_Seq_Id = " + persistentInstance.getCampSeqId();
		String sql3 = "delete from " + db + ".CAMP_EMP_APPD where camp_Seq_Id = " + persistentInstance.getCampSeqId();
		String sql4 = "update " + db + ".CAMP_CUST_APPD_RSLT_TEMP set camp_Seq_Id=0 " +
				"where camp_Seq_Id = " + persistentInstance.getCampSeqId();
		try {
			dao.delete(sql1);
			dao.delete(sql2);
			dao.delete(sql3);
			dao.update(sql4);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampCampSeq findById(String id) {

		String sql = "select * from " + db
				+ ".Camp_Camp_Seq where Camp_Seq_Id = " + id;
		CampCampSeq sd = new CampCampSeq();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setCampSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCampId(rs.getInt(3));
				sd.setCampSeqName(rs.getString(4));
				sd.setCallerNum(rs.getInt(5));
				sd.setTargetCustNum(rs.getInt(6));
				sd.setIsAppointStaff(rs.getString(7));
				sd.setIsAllocateList(rs.getString(8));
				sd.setIsCampStart(rs.getString(9));
				sd.setRemark(rs.getString(10));
				sd.setCreatorName(rs.getString(11));
				sd.setCreateTime(rs.getString(12));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Camp_Seq";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCampSeq sd = new CampCampSeq();
				sd.setCampSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCampId(rs.getInt(3));
				sd.setCampSeqName(rs.getString(4));
				sd.setCallerNum(rs.getInt(5));
				sd.setTargetCustNum(rs.getInt(6));
				sd.setIsAppointStaff(rs.getString(7));
				sd.setIsAllocateList(rs.getString(8));
				sd.setIsCampStart(rs.getString(9));
				sd.setRemark(rs.getString(10));
				sd.setCreatorName(rs.getString(11));
				sd.setCreateTime(rs.getString(12));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}

	public List findAll(String campid) {

		String sql = "select * from " + db + ".Camp_Camp_Seq where camp_id="
				+ campid + " ORDER BY Create_Time DESC";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCampSeq sd = new CampCampSeq();
				sd.setCampSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCampId(rs.getInt(3));
				sd.setCampSeqName(rs.getString(4));
				sd.setCallerNum(rs.getInt(5));
				sd.setTargetCustNum(rs.getInt(6));
				sd.setIsAppointStaff(rs.getString(7));
				sd.setIsAllocateList(rs.getString(8));
				sd.setIsCampStart(rs.getString(9));
				sd.setRemark(rs.getString(10));
				sd.setCreatorName(rs.getString(11));
				sd.setCreateTime(rs.getString(12));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}

}
